package my.market.jdbc.dao;
import java.sql.SQLException;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.mchange.v2.c3p0.impl.NewPooledConnection;

import my.market.jdbc.utils.C3p0Utils;
import my.market.jdbc.dataClass.Goods;

public class GoodsDao {
	public List<Goods> searchGoodsByType(String type) throws SQLException {
		try {
			QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource());
			String sql = "select * from goods where type=?";
			List<Goods> goods = (List<Goods>) runner.query(sql, new BeanListHandler(Goods.class), new Object[] {type});
			return goods;
		}catch (SQLException e) {
			e.printStackTrace();
			// TODO: handle exception
		}
		return null;
	}
	//searchAll
	public List<Goods> searchAll() throws SQLException{
		try {
			QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource());
			String sql = "select * from goods";
			List<Goods> list = (List<Goods>) runner.query(sql, new BeanListHandler(Goods.class)) ;
			return list;
		}catch (SQLException e) {
			e.printStackTrace();// TODO: handle exception
		}
		return null;
	}
	//findGoods
	public List<Goods> findGood(String name) throws SQLException{
		QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource());
		String sql = "select * from goods where name=?";
		List<Goods> goods = (List<Goods>) runner.query(sql, new BeanListHandler(Goods.class), new Object[] {name});
		return goods;
	}
	public Goods findGoodById(String id) throws SQLException{
		QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource());
		String sql = "select * from goods where id=?";
		Goods good = (Goods) runner.query(sql, new BeanHandler(Goods.class), new Object[] {id});
		return good;
	}
	//insertUser
	public boolean insert(Goods good) throws SQLException{
		QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource());
		String sql = "insert into goods (name, type, price, salesnum, description, imageLocation, downLocation) values (?,?,?,?,?,?,?)";
		int num = runner.update(sql, new Object[] {good.getName(), good.getType(), good.getPrice(),
				good.getSalesnum(), good.getDescription(), good.getImageLocation(), good.getDownLocation()
		});
		if(num > 0) {
			return true;
		}
		return false;
	}
	//deleteUser
	public boolean deleteGood(int id) throws SQLException{
		QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource());
		String sql = "delete from goods where id=?";
		int num = runner.update(sql, id);
		if(num > 0) {
			return true;
		}
		return false;
	}
	//updateUser
	public boolean updateGood(Goods good) throws SQLException{
		QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource());
		String sql = "update goods set name=?, type=?, price=?, salesnum=?, description=?, imageLocation=?, downLocation=? where id=?";
		int num = runner.update(sql, new Object[] {good.getName(), good.getType(), good.getPrice(),
				good.getSalesnum(), good.getDescription(), good.getImageLocation(), good.getDownLocation(), good.getId()});
		if (num > 0) {
			return true;
		}
		return false;
	} 
}
